package cn.iocoder.yudao.framework.excel.core.util;

import cn.iocoder.yudao.framework.excel.core.handler.SelectSheetWriteHandler;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.converters.longconverter.LongStringConverter;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.WorkbookWriteHandler;
import com.alibaba.excel.write.handler.context.WorkbookWriteHandlerContext;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;

/**
 * Excel 工具类
 *
 * @author 芋道源码
 */
public class ExcelUtils {

    /**
     * 将列表以 Excel 响应给前端
     *
     * @param response  响应
     * @param filename  文件名
     * @param sheetName Excel sheet 名
     * @param head      Excel head 头
     * @param data      数据列表哦
     * @param <T>       泛型，保证 head 和 data 类型的一致性
     * @throws IOException 写入失败的情况
     */
    public static <T> void write(HttpServletResponse response, String filename, String sheetName,
                                 Class<T> head, List<T> data) throws IOException {
        // 输出 Excel
        EasyExcel.write(response.getOutputStream(), head)
                .autoCloseStream(false) // 不要自动关闭，交给 Servlet 自己处理
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 基于 column 长度，自动适配。最大 255 宽度
                .registerWriteHandler(new SelectSheetWriteHandler(head)) // 基于固定 sheet 实现下拉框
                .registerConverter(new LongStringConverter()) // 避免 Long 类型丢失精度
                .sheet(sheetName).doWrite(data);
        // 设置 header 和 contentType。写在最后的原因是，避免报错时，响应 contentType 已经被修改了
        response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, StandardCharsets.UTF_8.name()));
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
    }

    public static <T> List<T> read(MultipartFile file, Class<T> head) throws IOException {
        return EasyExcel.read(file.getInputStream(), head, null)
                .autoCloseStream(false)  // 不要自动关闭，交给 Servlet 自己处理
                .doReadAllSync();
    }

    public static <T> void writes(HttpServletResponse response, String filename,
                                  Class<T> head, Map<String,List<T>> data, String printTime) throws IOException {

        Set<String> keys = data.keySet();
        ExcelWriterBuilder excelWriterBuilder= EasyExcel.write(response.getOutputStream());
        ExcelWriter excelWriter = excelWriterBuilder.build();
        List<List<String>> headList=new ArrayList<>();
        headList.add(createHead("序号"));
        headList.add(createHead("姓名"));
        headList.add(createHead("所在单位"));
        headList.add(createHead("身份代码"));
        headList.add(createHead("电话"));
        headList.add(createHead("职称"));
        headList.add(createHead("专业"));
        headList.add(createHead("抽取轮次"));
        headList.add(createHead("抽取时间"));
        headList.add(createHead("评委签名"));
        headList.add(createHead("备注"));

        for(String key:keys) {
            // 输出 Excel
            WriteSheet writeSheet = EasyExcel.writerSheet(key)
                    .registerWriteHandler(new TitleAndFooterWriteHandler(key, printTime, data.get(key).size(), head))
//                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 自动适配列宽
//                    .registerWriteHandler(new SelectSheetWriteHandler(head)) // 下拉框逻辑（若不需要可移除）
                    .registerWriteHandler(new ColumnWidthWriteHandler()) // 注册列宽设置处理器
                    .registerConverter(new LongStringConverter()) // 避免 Long 类型丢失精度
                    .build();
            excelWriter.write(Arrays.asList(new SheetTitleRow(key)), writeSheet);
// 2. 创建并配置数据表
            WriteTable writeTable = new WriteTable();
            writeTable.setHead(headList); // 设置表头

            excelWriter.write(data.get(key), writeSheet,writeTable);

        }
        excelWriter.finish();
        // 设置响应头，最后处理避免报错时已修改 contentType
        response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, StandardCharsets.UTF_8.name()));
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
    }
    // 创建表头单元格
    private static List<String> createHead(String headName) {
        List<String> head = new ArrayList<>();
        head.add(headName);
        return head;
    }
    /**
     * Sheet标题行模型
     */
    public static class SheetTitleRow {
        @ExcelProperty(value = "表名", index = 0)
        private String title;

        public SheetTitleRow(String title) {
            this.title = title;
        }

        public String getTitle() { return title; }
        public void setTitle(String title) { this.title = title; }
    }
    /**
     * 自定义处理器，修复页脚不显示问题：
     * 1. 动态计算表头列数（替代写死的 9 列）
     * 2. 修正页脚触发条件（根据数据行数动态定位）
     * 3. 样式与合并区域动态适配
     */
    static class TitleAndFooterWriteHandler implements SheetWriteHandler, RowWriteHandler {
        private final String title;
        private final String printTime;
        private final int dataSize;
        private final Class<?> headClass;
        private int headerRowIndex = 1; // 表头行索引（默认第 2 行，索引 1 ）
        private int columnCount = 11; // 默认假设11列，可根据实际情况调整
        private CellStyle titleStyle;
        private CellStyle headerStyle;
        private CellStyle dataStyle;
        private CellStyle dataStyle1;
        private CellStyle footerStyleRight; // 打印时间样式（居右）
        private CellStyle footerStyleLeft;  // 签字栏样式（居左）

        public TitleAndFooterWriteHandler(String title, String printTime, int dataSize, Class<?> headClass) {
            this.title = title;
            this.printTime = printTime;
            this.dataSize = dataSize;
            this.headClass = headClass;
        }

        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
            Sheet sheet = writeSheetHolder.getSheet();
            Workbook workbook = sheet.getWorkbook();

            // 初始化样式
            initStyles(workbook);

            // 创建标题行（第 1 行，索引 0 ）
            Row titleRow = sheet.createRow(0);
            Cell titleCell = titleRow.createCell(0);
            titleCell.setCellValue(title);


//            // 动态计算表头列数（替代写死的 9 列）
            //int columnCount = 10;
//            // 合并标题行：覆盖所有表头列
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnCount-1));
            titleCell.setCellStyle(titleStyle);
        }

        @Override
        public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                    Integer rowIndex, Integer relativeRowIndex, Boolean isHead) {
            // 自动识别表头行索引（适配动态表头）
            if (isHead) {
                headerRowIndex = rowIndex;
            }
        }

        @Override
        public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                   Row row, Integer relativeRowIndex, Boolean isHead) {
            // 确保表头行有足够的单元格
//            if (isHead && row.getRowNum() == headerRowIndex) {
//                // 动态检测列数（如果需要）
//                if (row.getLastCellNum() < 0) {
//                    for (int i = 0; i < columnCount; i++) {
//                        row.createCell(i);
//                    }
//                }
//
//                for (int i = 0; i < columnCount; i++) {
//                    Cell cell = row.getCell(i);
//                    if (cell == null) {
//                        cell = row.createCell(i);
//                    }
//                    cell.setCellStyle(headerStyle);
//                }
//            }
//            // 确保数据行有足够的单元格
//            else if (row.getRowNum() > headerRowIndex) {
//                for (int i = 0; i < columnCount; i++) {
//                    Cell cell = row.getCell(i);
//                    if (cell == null) {
//                        cell = row.createCell(i);
//                    }
//                    cell.setCellStyle(dataStyle);
//                }
//            }
        }

        @Override
        public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                    Row row, Integer relativeRowIndex, Boolean isHead) {
            Sheet sheet = writeSheetHolder.getSheet();
            Workbook workbook = sheet.getWorkbook();
            // 确保表头行有足够的单元格
            if (isHead && row.getRowNum() == headerRowIndex) {
                // 动态检测列数（如果需要）
                if (row.getLastCellNum() < 0) {
                    for (int i = 0; i < columnCount; i++) {
                        row.createCell(i);
                    }
                }

                for (int i = 0; i < columnCount; i++) {
                    Cell cell = row.getCell(i);
                    if (cell == null) {
                        cell = row.createCell(i);
                    }
                    cell.setCellStyle(headerStyle);
                }
            }
            // 确保数据行有足够的单元格
            else if (row.getRowNum() > headerRowIndex) {
                for (int i = 0; i < columnCount; i++) {
                    Cell cell = row.getCell(i);
                    if (cell == null) {
                        cell = row.createCell(i);
                    }
                    if(i == 8){
                        cell.setCellStyle(dataStyle1);
                    }else{
                        cell.setCellStyle(dataStyle);
                    }
                }
            }
            //设置标题A1单元格样式
            Row row0=sheet.getRow(0);
            Cell cell0=row0.getCell(0);
            cell0.setCellStyle(titleStyle);
            // 动态计算表头列数
            //int columnCount = 10;
            // 页脚起始行 = 表头行 + 数据行数 + 1（空行分隔）
            int footerStartRow = headerRowIndex + dataSize + 2;

            // 当处理到“数据最后一行”时，创建页脚
            if (row.getRowNum() == headerRowIndex + dataSize) {
                // 1. 打印时间行（居右，合并所有列）
                Row printTimeRow = sheet.createRow(footerStartRow);
                printTimeRow.setHeight((short) (30 * 20)); // 设置打印时间行行高为 24
                Cell printTimeCell = printTimeRow.createCell(0);
                printTimeCell.setCellValue("打印时间：" + printTime);
                printTimeCell.setCellStyle(footerStyleRight);
                sheet.addMergedRegion(new CellRangeAddress(footerStartRow, footerStartRow, 0, columnCount-1 ));



                // 2. 签字栏行（居左，合并所有列）
                Row signatureRow = sheet.createRow(footerStartRow + 1);
                signatureRow.setHeight((short) (49 * 20)); // 设置参加抽取评委人员签字行行高为 49
                Cell signatureCell = signatureRow.createCell(0);
                signatureCell.setCellValue("参加抽取评委人员签字：");
                signatureCell.setCellStyle(footerStyleLeft);
                sheet.addMergedRegion(new CellRangeAddress(footerStartRow + 1, footerStartRow + 1, 0, columnCount-1 ));

            }
        }

        /**
         * 初始化样式：标题、表头、数据行、页脚
         */
        private void initStyles(Workbook workbook) {
            // 标题样式：加粗、居中、字号
            titleStyle = workbook.createCellStyle();
            Font titleFont = workbook.createFont();
            titleFont.setFontName("宋体");
            titleFont.setBold(true);
            titleFont.setFontHeightInPoints((short) 16);
            titleStyle.setFont(titleFont);
            titleStyle.setAlignment(HorizontalAlignment.CENTER);
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            // 表头样式：灰色背景、加粗、居中、边框
            headerStyle = workbook.createCellStyle();
            Font headerFont = workbook.createFont();
            headerFont.setFontName("宋体");
            headerFont.setBold(true);
            headerFont.setFontHeightInPoints((short) 9); // 表头字体大小改为12
            headerStyle.setFont(headerFont);
            headerStyle.setAlignment(HorizontalAlignment.CENTER);
            headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headerStyle.setBorderTop(BorderStyle.THIN);
            headerStyle.setBorderBottom(BorderStyle.THIN);
            headerStyle.setBorderLeft(BorderStyle.THIN);
            headerStyle.setBorderRight(BorderStyle.THIN);

            // 数据行样式：居中、边框
            dataStyle = workbook.createCellStyle();
            Font dataFont = workbook.createFont();
            dataFont.setFontName("宋体");
            dataFont.setFontHeightInPoints((short) 9);
            dataStyle.setFont(dataFont);
            dataStyle.setAlignment(HorizontalAlignment.CENTER); // 数据行水平居中
            dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            dataStyle.setBorderTop(BorderStyle.THIN);
            dataStyle.setBorderBottom(BorderStyle.THIN);
            dataStyle.setBorderLeft(BorderStyle.THIN);
            dataStyle.setBorderRight(BorderStyle.THIN);

            dataStyle1 = workbook.createCellStyle();
            Font dataFont1 = workbook.createFont();
            dataFont1.setFontName("宋体");
            dataFont1.setFontHeightInPoints((short) 9);
            dataStyle1.setFont(dataFont1);
            dataStyle1.setAlignment(HorizontalAlignment.CENTER); // 数据行水平居中
            dataStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
            dataStyle1.setBorderTop(BorderStyle.THIN);
            dataStyle1.setBorderBottom(BorderStyle.THIN);
            dataStyle1.setBorderLeft(BorderStyle.THIN);
            dataStyle1.setBorderRight(BorderStyle.THIN);
            // 添加时间格式支持
            DataFormat dataFormat = workbook.createDataFormat();
            // 日期时间格式：yyyy-mm-dd hh:mm:ss
            dataStyle1.setDataFormat(dataFormat.getFormat("yyyy-mm-dd hh:mm:ss"));

            // 页脚（打印时间）样式：居右、小字号
            footerStyleRight = workbook.createCellStyle();
            Font footerFontRight = workbook.createFont();
            footerFontRight.setFontName("宋体");
            footerFontRight.setFontHeightInPoints((short) 9);
            footerStyleRight.setFont(footerFontRight);
            footerStyleRight.setAlignment(HorizontalAlignment.RIGHT);
            footerStyleRight.setVerticalAlignment(VerticalAlignment.CENTER);
//            footerStyleRight.setBorderTop(BorderStyle.THIN);
//            footerStyleRight.setBorderBottom(BorderStyle.THIN);
//            footerStyleRight.setBorderLeft(BorderStyle.THIN);
//            footerStyleRight.setBorderRight(BorderStyle.THIN);

            // 页脚（签字栏）样式：居左、小字号
            footerStyleLeft = workbook.createCellStyle();
            Font footerFontLeft = workbook.createFont();
            footerFontLeft.setFontName("宋体");
            footerFontLeft.setFontHeightInPoints((short) 9);
            footerStyleLeft.setFont(footerFontLeft);
            footerStyleLeft.setAlignment(HorizontalAlignment.LEFT);
            footerStyleLeft.setVerticalAlignment(VerticalAlignment.CENTER);
//            footerStyleLeft.setBorderTop(BorderStyle.THIN);
//            footerStyleLeft.setBorderBottom(BorderStyle.THIN);
//            footerStyleLeft.setBorderLeft(BorderStyle.THIN);
//            footerStyleLeft.setBorderRight(BorderStyle.THIN);
        }
    }

    /**
     * 列宽设置处理器
     */
    static class ColumnWidthWriteHandler implements SheetWriteHandler {

        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
            Sheet sheet = writeSheetHolder.getSheet();
            // 设置序号列宽为 5
            sheet.setColumnWidth(0, 5 * 256);
            // 设置姓名、所在单位、身份代码、电话、职称、专业、抽取轮次、评委签名列宽为 11
            for (int i = 1; i <= 7; i++) {
                sheet.setColumnWidth(i, 11 * 256);
            }
            // 设置抽取时间列宽为 16
            sheet.setColumnWidth(8, 20 * 256);
            sheet.setColumnWidth(9, 11 * 256);
            // 设置备注列宽为 11
            sheet.setColumnWidth(10, 11 * 256);
        }
    }
}
